import pandas as pd
import openpyxl


def get_subdomains_from_excel(file_path) -> list:
    df = pd.ExcelFile(file_path)
    sheet_names = df.sheet_names
    domain_sheet_names = [x for x in sheet_names if 'domain-' in x]

    domains = []
    for sheet in domain_sheet_names:
        df_one = pd.read_excel(file_path, sheet_name=sheet)
        domains.extend(list(df_one['子域名'].drop_duplicates()))

    return domains


def get_ip_from_excel(file_path) -> list:
    df = pd.ExcelFile(file_path)
    sheet_names = df.sheet_names
    domain_sheet_names = [x for x in sheet_names if 'domain-' in x]

    ips = []
    for sheet in domain_sheet_names:
        df_one = pd.read_excel(file_path, sheet_name=sheet)
        ips.extend(list(df_one['解析结果'].drop_duplicates()))
    return list(set(ips))


def sheet_compine(df: pd.DataFrame, file_path, sheet_name, subset):
    df1 = pd.read_excel(file_path, sheet_name=sheet_name)
    result = pd.concat([df1, df], ignore_index=True)
    result = result.drop_duplicates(subset=subset, keep='last')
    book = openpyxl.load_workbook(file_path)
    std = book.get_sheet_by_name(sheet_name)
    book.remove(std)
    xlsx = pd.ExcelWriter(file_path, engine='openpyxl')
    xlsx.book = book
    result.to_excel(xlsx, sheet_name=sheet_name, index=False)
    xlsx.save()
    xlsx.close()